using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.DirectoryServices;
using System.Data.SqlClient;
using System.Reflection;
using System.Xml;
using Microsoft.Win32;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace eBirdAttend
{
    public partial class ConfigFrm : Form
    {
        ServerConnection m_ServerConnection;
        Server m_Server;
        EventHandler _Handler;

        string ConnectionString = "";
        string ConnectionString2 = "";
        public ConfigFrm()
        {
            InitializeComponent();
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void ConfigFrm_Load(object sender, EventArgs e)
        {
            try
            {

                cmbDatabase.SelectedText = Properties.Settings.Default["Database"].ToString();
                cmbServer.SelectedText = Properties.Settings.Default["DBServer"].ToString();
                txtUser.Text = Properties.Settings.Default["User"].ToString();
                txtPwd.Text = Properties.Settings.Default["PWD"].ToString();
                comboBoxSerialPort.SelectedText = Properties.Settings.Default["PORT"].ToString();
                ConnectionString2 = Properties.Settings.Default["ConnectionString"].ToString();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message.ToString());
            }

            
      
       }

        public void FillServers()
        {
          //  this.Cursor = Cursors.WaitCursor; 
            //if(cmbServer.Items.Count>0)
            this.cmbServer.Items.Clear();

            DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
            dtServers.PrimaryKey = new DataColumn[] { dtServers.Columns[0] };
            this.cmbServer.DataSource = dtServers;
            this.cmbServer.DisplayMember = "Name";
            this.cmbServer.ValueMember = "Name";

           
           //     this.cmbServer.SelectedIndex = 0;

        }
        //public void FillServers()
        //{
        //    try
        //    {
        //        string strName = "";

        //        SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
        //        SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
        //        for (int i = 0; i < sqlServers.Count; i++)
        //        {
        //            object srv = sqlServers.Item(i + 1);
        //            if (srv != null)
        //            {
        //                this.cmbServer.Items.Add(srv);
        //            }
        //        }

        //        if (cmbServer.Items.Count == 0)
        //        {
        //            strName = System.Net.Dns.GetHostName();
        //            cmbServer.Items.Add(strName);
        //        }

        //        cmbServer.SelectedIndex = 0;

        //        //try
        //        //{
        //        //    int n = 0;
        //        //    string strName = "";

        //        //    NetworkBrowser nb = new NetworkBrowser();
        //        //    foreach (string pc in nb.getNetworkComputers())
        //        //    {
        //        //        cmbServer.Items.Add(pc);
        //        //        n = 1;
        //        //    }
        //        //    if (n != 1)
        //        //    {
        //        //        strName = System.Net.Dns.GetHostName();
        //        //        cmbServer.Items.Add(strName);
        //        //    }
        //        //    cmbServer.SelectedIndex = 0;
        //        //}
        //        //catch (Exception ex)
        //        //{
        //        //    MessageBox.Show("An error occurred trying to access the network computers", "error",
        //        //                       MessageBoxButtons.OK, MessageBoxIcon.Error);
        //        //}
        //    }
        //    catch (Exception exp)
        //    {

        //        MessageBox.Show(exp.Message.ToString());
        //    }

        //}


        public void FillDatabases()
        {
            try
            {
                String conxString = "Data Source=" + cmbServer.SelectedItem + " ;Integrated Security=True;";

                using (SqlConnection sqlConx = new SqlConnection(conxString))
                {
                    sqlConx.Open();
                    DataTable tblDatabases = sqlConx.GetSchema("Databases");
                    sqlConx.Close();

                    foreach (DataRow row in tblDatabases.Rows)
                    {
                        cmbDatabase.Items.Add(row["database_name"].ToString());
                    }
                }
                cmbDatabase.SelectedIndex = 0;
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message.ToString());
            }
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
            try
            {string strServer,strDatabase;
                
                if (cmbServer.SelectedItem == null) strServer = cmbServer.Text;
                else strServer = cmbServer.SelectedItem.ToString();
                if (cmbDatabase.SelectedItem == null) strDatabase = cmbDatabase.Text;
                else strDatabase = cmbDatabase.SelectedItem.ToString();
                ConnectionString = "Data Source=" + strServer;
                ConnectionString = ConnectionString + ";Initial Catalog=" + strDatabase;
                ConnectionString = ConnectionString + ";User ID=" + txtUser.Text;
                ConnectionString = ConnectionString + ";Password=" + txtPwd.Text;
                srv.Connect(cmbServer.Text, txtUser.Text, txtPwd.Text);
                MessageBox.Show("Connection Successful \nSaving.....","Connection",MessageBoxButtons.OK,MessageBoxIcon.Information);
                //Provider=SQLOLEDB.1;Data Source=DEVIKA;Persist Security Info=True;Password=bmjobmjo;User ID=sa;Initial Catalog=eOffice
                ConnectionString2 = "Provider=SQLOLEDB.1;";
                ConnectionString2 = ConnectionString2 + ConnectionString + ";Persist Security Info=True";
                WriteToAppConfig();
                this.Dispose();
                
            }
            catch (Exception e1)
            {
                if(MessageBox.Show("Connection Failed. Save anyway?","Fail",MessageBoxButtons.YesNo)==DialogResult.Yes)
                    WriteToAppConfig();

            }
        }

     
        public void WriteToAppConfig()
        {
            Properties.Settings.Default["ConString"] = ConnectionString;
            Properties.Settings.Default["Database"] = cmbDatabase.Text;
            Properties.Settings.Default["DBServer"] = cmbServer.Text;
            Properties.Settings.Default["User"] = txtUser.Text;
            Properties.Settings.Default["PWD"] = txtPwd.Text;
            Properties.Settings.Default["PORT"] = comboBoxSerialPort.Text;
            Properties.Settings.Default["ConnectionString"] = ConnectionString2;
            Properties.Settings.Default.ConTmp1 = ConnectionString;
            Properties.Settings.Default.ConTmp2 = ConnectionString2;
            Properties.Settings.Default.Save(); 

            try
            {
                //Registry.....
                //Create new key
                RegistryKey regKey = Registry.CurrentUser.CreateSubKey("SOFTWARE\\eBird\\eOffice1.0");
                regKey.SetValue("SQLServer", cmbServer.Text);
                regKey.SetValue("Database", cmbDatabase.Text);
                regKey.SetValue("User", txtUser.Text);
                regKey.SetValue("Passwd", txtPwd.Text);
                regKey.SetValue("ConctString", ConnectionString);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }

           

          //// System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly ();
          //  string strFile = Application.ExecutablePath;
          //  string strFilePath = @"D:\priya\bmjo\eBirdAttend\App.config";
          //  string executableName = Directory.GetCurrentDirectory();
        
          // System.IO.FileInfo fi = new System.IO.FileInfo(strFilePath);

          //  if (!fi.Exists)
          //      throw new Exception ("Missing config file");

          //  // load the config file into the XML DOM.
          //  System.Xml.XmlDocument xmlConfig = new System.Xml.XmlDocument();
          //  xmlConfig.PreserveWhitespace = true;
          //  xmlConfig.Load (fi.FullName);

          //  // find the right node and change it to the new value
          //  foreach (System.Xml.XmlNode node in xmlConfig["configuration"]["connectionStrings"])
          //  {
          //      if ((node.Name == "add") && (node.Attributes.GetNamedItem("name").Value == "eBirdAttend.Properties.Settings.ebirdatsConnectionString2"))
          //      {
          //          node.Attributes.GetNamedItem("connectionString").Value = ConnectionString;
          //      }

          //  }

          //  // write out the new config file
          //  xmlConfig.Save (fi.FullName);

        }

        private void buttonFindServer_Click(object sender, EventArgs e)
        {

            FillServers();
        }
        private void ListDatabasesInServer()
        {
            this.cmbDatabase.Items.Clear();
            // Loop through the databases list
            foreach (Database db in this.m_Server.Databases)
            {
                //We don't want to be adding the System databases to our list
                //Check if database is system database
                if (!db.IsSystemObject)
                {
                    this.cmbDatabase.Items.Add(db.Name); // Add database to combobox
                }
            }
            this.cmbDatabase.SelectedIndex = 0;
            //We have the list now
        }

        private string ConnectDatabase()
        {
            if (!string.IsNullOrEmpty(this.cmbServer.Text))
            {
                try
                {
                    this.m_ServerConnection = new ServerConnection(this.cmbServer.Text.ToString());
                    //this.m_ServerConnection
                    //First Check type of Authentication
                    
                        // Create a new connection to the selected server name
                        this.m_ServerConnection.LoginSecure = false;
                        this.m_ServerConnection.Login = this.txtUser.Text;       //Login User
                        this.m_ServerConnection.Password = this.txtPwd.Text;    //Login Password
                        this.m_ServerConnection.DatabaseName = this.cmbDatabase.Text;  //Database Name
                        // Create a new SQL Server object using the connection we created
                        this.m_Server = new Server(this.m_ServerConnection);
                    
                    return string.Empty;
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            return "No server selected";
        }
        public void ListDB()
        {
            this.Cursor = Cursors.WaitCursor;
            try
            {
                if (!string.IsNullOrEmpty(this.cmbServer.Text))
                {
                    string message = this.ConnectDatabase();
                    if (string.IsNullOrEmpty(message))
                    {
                        //This will populate list of databases on selected server
                        this.ListDatabasesInServer();
                    }
                    else
                    {
                        MessageBox.Show(message, "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        this.Cursor = Cursors.Default;
                        return;
                    }
                }
                else
                {
                    // A server was not selected, show an error message
                    this.Cursor = Cursors.Default;
                    MessageBox.Show("Please select a server first", "Server not selected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
            }
            catch (Exception ex)
            {
                this.Cursor = Cursors.Default;
                MessageBox.Show(ex.Message, "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            this.Cursor = Cursors.Default;
        }
        private void buttonListDB_Click(object sender, EventArgs e)
        {
            if (txtUser.Text.Trim() == string.Empty || txtUser.Text.Trim() == "" || txtUser.Text.Trim() == null)
            {
                MessageBox.Show("Enter UserName");
                txtUser.Focus();
                return;
            }
            else if (txtPwd.Text.Trim() == string.Empty || txtPwd.Text.Trim() == "" || txtPwd.Text.Trim() == null)
            {
                MessageBox.Show("Enter Password");
                txtUser.Focus();
                return;
            }
           // FillDatabases();
            ListDB();
        }

        private void cmbServer_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

      

    }
}